package cc.ucanuup.service.base.dao;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import cc.ucanuup.service.base.entity.UpMenus;

/**
 * 文件名： com.bozhon.srm.service.base.datamanager.UpMenusJPA.java
 * 作者：   WenBin
 * 日期：   2018年2月5日
 * 功能说明：
 *
 * =========================================================
 * 修改记录：
 * 修改作者    日期      修改内容
 * =========================================================
 *  Copyright (c) 2010-2011 .All rights reserved.
 */
public interface UpMenusJPA  extends JpaRepository<UpMenus, Serializable>,JpaSpecificationExecutor<UpMenus>{

	/**
	 * @Method: com.bozhon.srm.service.base.datamanager.UpMenusJPA.queryTopMenusAndSortIt
	 * @Description:  查询最顶端的菜单信息并排序
	 * @author: WenBin
	 * @date: 2018年2月5日
	 * @version: 1.0
	 * @param pageable
	 * @return
	 * Page<UpMenus>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value="SELECT T FROM UpMenus T where T.menuLevel = '0' AND T.deleted = 0 ORDER BY T.orderLevel ASC",countQuery="SELECT COUNT(1) FROM UpMenus T where T.menuLevel = '0' AND T.deleted = 0")
	Page<UpMenus> queryTopMenusAndSortIt(Pageable pageable);

	/**
	 * @Method: com.bozhon.srm.service.base.datamanager.UpMenusJPA.findMenusByLevel
	 * @Description:  通过菜单等级查询菜单信息
	 * @author: WenBin
	 * @date: 2018年2月5日
	 * @version: 1.0
	 * @param menuLevel
	 * @return
	 * List<UpMenus>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value="SELECT T FROM UpMenus T where T.menuLevel = :menuLevel AND T.deleted = 0")
	List<UpMenus> findMenusByLevel(@Param("menuLevel")Integer menuLevel);

	/**
	 * @Method: com.bozhon.srm.service.base.datamanager.UpMenusJPA.findMenusByFid
	 * @Description: 通过父菜单查询子菜单的信息
	 * @author: WenBin
	 * @date: 2018年2月5日
	 * @version: 1.0
	 * @param fid
	 * @return
	 * List<UpMenus>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value="SELECT T FROM UpMenus T where T.fid = :fid AND T.deleted = 0")
	List<UpMenus> findMenusByFid(@Param("fid")String fid);

	/**
	 * @Method: com.bozhon.srm.service.base.datamanager.UpMenusJPA.findMenusById
	 * @Description:  通过菜单ID查询所有的子菜单信息
	 * @author: WenBin
	 * @date: 2018年2月5日
	 * @version: 1.0
	 * @param id
	 * @return
	 * List<UpMenus>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value="SELECT T.*  from TBUP_MENUS T WHERE T.DELETED = 0  start with T.ID = :id connect by prior T.ID = T.FID ",nativeQuery=true)
	List<UpMenus> findMenusById(@Param("id")String id);

	/**
	 * @Method: com.bozhon.service.base.datamanager.MenuJPA.findAllActiveMenus
	 * @Description: 查询出所有激活的菜单信息
	 * @author: WenBin
	 * @date: 2017年10月16日
	 * @version: 1.0
	 * @return
	 * List<Menu>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value="SELECT T FROM UpMenus T where T.deleted = 0 AND T.status = 1")
	List<UpMenus> findAllActiveMenus();


	/**
	 * @Method: com.bozhon.service.base.datamanager.MenuJPA.findMenusByRoleId
	 * @Description: 通过角色主键找到所有的权限菜单
	 * @author: WenBin
	 * @date: 2017年10月16日
	 * @version: 1.0
	 * @param roleId  角色主键
	 * @return
	 * List<Menu>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value=" SELECT DISTINCT TM.* FROM TBUP_ROLE TR  "
			+"   LEFT JOIN RELATION_ROLE_MENUS  RM ON RM.ROLE_ID = TR.ID  "
			+"   LEFT JOIN TBUP_MENUS TM ON TM.ID = RM.MENU_ID  "
			+"   WHERE TR.ID = :roleId "
			,nativeQuery=true)
	List<UpMenus> findMenusByRoleId(@Param("roleId")String roleId);

	@Query(value=" SELECT DISTINCT TM.* FROM TBUP_MENUS TM"
			+ " LEFT JOIN  RELATION_ROLE_MENUS  RM ON RM.MENU_ID = TM.ID "
			+ " LEFT JOIN  TBUP_ROLE TR ON TR.ID =  RM.ROLE_ID "
			+ " LEFT JOIN  RELATION_USER_ROLE  SR ON SR.ROLE_ID = TR.ID "
			+ " LEFT JOIN  TBUP_USER TS ON TS.ID = SR.USER_ID "
			+ " WHERE TS.USER_ACCOUNT = :userAccount "
			,nativeQuery=true)
	List<UpMenus> findAllActiveMenusByUserAccount(@Param("userAccount") String userAccount);

	/**
	 * @Method: com.bozhon.srm.service.base.datamanager.UpMenusJPA.findDefaultResourceWithType
	 * @Description: 通过权限类型查询用户的菜单信息
	 * @author: WenBin
	 * @date: 2018年2月7日
	 * @version: 1.0
	 * @param roleType
	 * @return
	 * List<UpMenus>
	 * @update [日期YYYY-MM-DD] [更改人姓名][变更描述]
	 */
	@Query(value="SELECT  TRES.* FROM TBUP_MENUS TRES  "
			+ " LEFT JOIN RELATION_ROLE_MENUS SRR  ON TRES.ID = SRR.MENU_ID    "
			+ " LEFT JOIN TBUP_ROLE TR ON SRR.ROLE_ID = TR.ID  "
			+ " WHERE TR.POWER_TYPE = ?1 ",nativeQuery=true)
	List<UpMenus> findDefaultResourceWithType(String roleType);
}
